﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class BankManager:ConnectionManager
    {
        #region Adding/Updating
        public SqlException AddBank(BankUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO OBNK (BankCode, BankName) VALUES (@BankCode, @BankName)";
                oCommand.Parameters.Add(new SqlParameter("@BankCode", oUnit.BankCode));
                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBank(BankUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OBNK SET  BankName = @BankName WHERE BankCode=@BankCode";
                oCommand.Parameters.Add(new SqlParameter("@BankCode", oUnit.BankCode));
                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateBank(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO OBNK (BankCode, BankName, DocStatus) VALUES (@BankCode, @BankName, @DocStatus)";
                        oCommand.Parameters.Add(new SqlParameter("@BankCode", oRow["BankCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DocStatus", oRow["DocStatus"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                    if (oRow.RowState == DataRowState.Modified)
                    {

                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "UPDATE OBNK SET  BankName = @BankName, BankCode=@BankCode, DocStatus=@DocStatus WHERE BankNo=@BankNo";

                        oCommand.Parameters.Add(new SqlParameter("@BankNo", oRow["BankNo"]));
                        oCommand.Parameters.Add(new SqlParameter("@BankCode", oRow["BankCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@BankName", oRow["BankName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DocStatus", oRow["DocStatus"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion

        #region Retreive Data

        public DataTable GetBanks()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBanksByName(string sBankName)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sBankName = "%" + sBankName.Trim() + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK WHERE BankName LIKE @BankName";

            oCommand.Parameters.Add(new SqlParameter("@BankName", sBankName));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseBanksByCode(string sBankCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sBankCode = "%" + sBankCode.Trim() + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK WHERE BankCode LIKE @BankCode";

            oCommand.Parameters.Add(new SqlParameter("@BankCode", sBankCode));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetByBankCode(string sBankCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OBNK WHERE BankCode = @BankCode";

            oCommand.Parameters.Add(new SqlParameter("@BankCode", sBankCode));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public bool IsBankExists(string sBank)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM OBNK WHERE BankCode=@BankCode";

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(dt);
                int iRowCount = 0;

                try
                {
                    iRowCount = dt.Rows.Count;
                }
                catch
                {
                    iRowCount = 0;
                }


                if (iRowCount >= 1)
                {
                    return true;
                }

                if (iRowCount >= 1)
                {
                    return true;
                }


                return false;


            }
            catch
            {
                return false;
            }
        }

        #endregion

    }
}
